"""
Case Type   : 系统表
Case Name   : 测试系统表GS_AUDITING_POLICY_ACCESS字段与数据类型是否正确
Create At   :2024/07/08
Owner       :sungang14
Description : 
    1.查看系统表GS_AUDITING_POLICY_ACCESS的表结构
    \d GS_AUDITING_POLICY_ACCESS
    2.该表字段与对应字段数据类型是否如下：
     accesstype | name        
     labelname  | name                     
     policyoid  | oid                        
     modifydate | timestamp without time zone
    3、查看表隐含属性oid
    select oid from GS_AUDITING_POLICY_ACCESS; 
    4、验证非系统管理员或安全策略管理员权限访问此系统表
    创建普通用户"create user t_systemtable_0102 password 'Huawei@123'"
    使用t_systemtable_0102用户访问系统表"select * from GS_AUDITING_POLICY_ACCESS;"
Expect      : 
    1.查看系统表GS_AUDITING_POLICY_ACCESS表结构成功
    2.该表字段与字段数据类型对应正确
    3.存在隐含属性oid
    4.无权限访问
History     : 
"""

import sys
import os
import unittest
from yat.test import Node
from yat.test import macro

sys.path.append(sys.path[0] + "/../")
from testcase.utils.Logger import Logger
from testcase.utils.Common import Common
from testcase.utils.CommonSH import CommonSH


class IndexFileDamaged(unittest.TestCase):
    def setUp(self):
        self.logger = Logger()
        self.logger.info(f'-----{os.path.basename(__file__)} start-----')
        self.logger.info('----------------this is setup-----------------------')
        self.userNode = Node('dbuser')
        self.DB_ENV_PATH = macro.DB_ENV_PATH
        self.DB_INSTANCE_PATH = macro.DB_INSTANCE_PATH
        self.com = Common()
        self.comsh = CommonSH('dbuser')
        self.expect_result_dict = {
            'Column': ['accesstype', 'labelname', 'policyoid', 'modifydate'],
            'Type': ['name', 'name', 'oid', 'timestamp without time zone']
        }
        self.comsh.execut_db_sql("drop user if exists t_systemtable_0102;")
        self.comsh.execut_db_sql("create user t_systemtable_0102 password 'Huawei@123';")

    def test_Index_file_damaged(self):
        self.logger.info('----------------------------查看表结构-----------------------------')
        msg = self.comsh.execut_db_sql('\d GS_AUDITING_POLICY_ACCESS')
        self.logger.info(msg)
        result_dict = self.com.format_sql_result(msg)
        self.logger.info(result_dict)
        del result_dict['Modifiers']
        self.assertDictEqual(self.expect_result_dict, result_dict)
        self.logger.info('查看表隐含属性oid')
        msg = self.comsh.execut_db_sql('select oid from GS_AUDITING_POLICY_ACCESS;')
        self.logger.info(msg)
        self.assertNotIn('ERROR', msg, '查看表隐含属性oid')
        self.logger.info("验证非系统管理员或安全策略管理员权限访问此系统表")
        msg = self.comsh.execut_db_sql("select * from GS_AUDITING_POLICY_ACCESS;",
                                       sql_type=f" -U t_systemtable_0102 -W Huawei@123")
        self.logger.info(msg)
        content = "ERROR:  permission denied for relation gs_auditing_policy_access\nDETAIL:  N/A"
        self.assertEqual(msg, content, "验证非系统管理员或安全策略管理员权限访问此系统表")

    def tearDown(self):
        self.logger.info('----------------this is tearDown-----------------------')
        # 删除用户
        msg = self.comsh.execut_db_sql("drop user if exists t_systemtable_0102;")
        self.logger.info(msg)
        self.assertEqual('DROP ROLE', msg)
        self.logger.info(f'-----{os.path.basename(__file__)} end-----')
